package com.gc.services.impl;

import com.gc.common.constants.FileConstants;
import com.gc.common.constants.SysConstants;
import com.gc.common.util.MyUtil;
import com.gc.dao.custom.ClientMapper;
import com.gc.dao.custom.VorAuditMapper;
import com.gc.dao.custom.VorMapper;
import com.gc.dto.AuditDTO;
import com.gc.dto.VorDTO;
import com.gc.dto.VorSubmitDTO;
import com.gc.pojo.*;
import com.gc.services.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.shiro.SecurityUtils;
import org.apache.shiro.subject.Subject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Slf4j
@Service
public class VorServiceImpl implements VorService {

    @Autowired
    private VorMapper vorMapper;
    @Autowired
    private VorAuditMapper vorAuditMapper;
    @Autowired
    private TemplateService templateService;
    @Autowired
    private DeviceService deviceService;
    @Autowired
    private ClientService clientService;
    @Autowired
    private UserService userService;
    @Autowired
    private UserAssociationService userAssociationService;

    @Override
    public int addVor(SysVor sysVor) {
        //warn 注意这里要返回主键id
        return vorMapper.insertSelective(sysVor);
    }

    @Override
    public int modifyVor(SysVor sysVor) {
      return  vorMapper.updateByPrimaryKeySelective(sysVor);
    }

    @Override
    public List<VorDTO> getMyVorList(Integer uid) {
           return vorMapper.selectVorByUId(uid);
    }

    @Override
    public SysVor getVorByVid(Integer vid) {
        return vorMapper.selectByPrimaryKey(vid);
    }

    @Override
    public SysVor getVorByVidAndUid(Integer vid, Integer uid) {
        return vorMapper.selectByVIdAndUId(vid,uid);
    }

    @Override
    public int removeVorByVId(Integer vid) {
        return vorMapper.deleteByPrimaryKey(vid);
    }

    @Override
    public List<VorDTO> getAllVorList() {
        return vorMapper.selectAll();
    }

    @Override
    @Transactional
    public int modifyVorTid(Integer tid,Integer vid) {
        int con1;
        int con2;
        try {
            if(tid != 1){   //warn  1 为空模板 , 所以还是当作没分配模板,  不用修改原始记录状态 , 当不为1时修改
                con1 = vorMapper.updateStatusByVId(vid, SysConstants.VOR_STATUS2);
                if(con1 != 1){
                    return 0;
                }
            }
            con2 = vorMapper.updateTIdByVId(tid,vid);
            if(con2 != 1){
                throw new Exception();
            }
        } catch (Exception e) {
            con2 = 0;
            e.printStackTrace();
        }
        return con2;
    }

    @Override
    public int modifyVorStatusByVId(Integer vid,Integer status) {
        return vorMapper.updateStatusByVId(vid,status);
    }

    @Override
    public List<VorDTO> getNotSaveVorList() {
        return vorMapper.selectNotSave();
    }

    @Override
    public int modifyReportNumber(Integer vid, String bgbh) {
        return vorMapper.updateReportNumber(vid,bgbh);
    }

    @Override
    public List<VorDTO> getVorByCid(Integer uid, Integer cid) {
        return vorMapper.selectVorByCidAndUid(uid,cid);
    }

    @Override
    public List<VorDTO> getVorByCid(Integer cid) {
        return vorMapper.selectVorByCid(cid);
    }

    @Override
    public List<VorDTO> getQfyVor(Integer userId) {
        return vorMapper.selectVorByQfyId(userId);
    }

    @Override
    public List<VorDTO> getShyVor(Integer userId) {
        return vorMapper.selectVorByShyId(userId);
    }

    @Override
    public int auditReport(AuditDTO auditDTO) {
        int count1 = vorMapper.updateStatusByVId(auditDTO.getVorId(),auditDTO.getStatus());
        SysVorAudit audit =  vorAuditMapper.selectByVorId(auditDTO.getVorId());
        if(auditDTO.getStatus().equals(SysConstants.VOR_STATUS2)){
            audit.setAuditTime(new Date());
        }else if(auditDTO.getStatus().equals(SysConstants.VOR_STATUS3)){
            audit.setAuditTime(new Date());
            audit.setAuditMsg(auditDTO.getMsg());
        }else if(auditDTO.getStatus().equals(SysConstants.VOR_STATUS4)){
            audit.setRatifyTime(new Date());
        }else if(auditDTO.getStatus().equals(SysConstants.VOR_STATUS5)){
            audit.setRatifyTime(new Date());
            audit.setRatifyMsg(auditDTO.getMsg());
        }
        int count2 = vorAuditMapper.updateByPrimaryKey(audit);
        return 0;
    }

    /**
     * 通用模板填充下载
     * @param info
     */
    @Override
    public void downVorExcel1(SysVor info, HttpServletResponse response) {
        XSSFWorkbook wb = null;
        try {
            wb = new XSSFWorkbook(new FileInputStream(FileConstants.TEMPLATE_LOCAL_URL +"1598605652095.xlsm"));
        } catch (IOException e) {
            e.printStackTrace();
        }
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFCell c8_0 = sheet.getRow(8).getCell(0);
        XSSFCell c13_9 = sheet.getRow(13).getCell(9);
        XSSFCell c37_0 = sheet.getRow(37).getCell(0);
        XSSFCell c65_0 = sheet.getRow(65).getCell(0);
        XSSFCell c101_0 = sheet.getRow(101).getCell(0);
        c13_9.setCellValue(info.getBgbh());
        c8_0.setCellValue(info.getBgbh());
        c37_0.setCellValue(info.getBgbh());
        c65_0.setCellValue(info.getBgbh());
        c101_0.setCellValue(info.getBgbh());
        XSSFCell c14_9 = sheet.getRow(14).getCell(9);
        c14_9.setCellValue(info.getWtDw());
        XSSFCell c16_9 = sheet.getRow(16).getCell(9);
        c16_9.setCellValue(info.getSbjbJxmc());
        XSSFCell c17_9 = sheet.getRow(17).getCell(9);
        c17_9.setCellValue(info.getSbjbJxxh());
        XSSFCell c18_9 = sheet.getRow(18).getCell(9);
        c18_9.setCellValue(info.getSbjbSccj());
        XSSFCell c67_5 = sheet.getRow(67).getCell(5);
        c67_5.setCellValue(info.getWtDw());
        XSSFCell c68_5 = sheet.getRow(68).getCell(5);
        c68_5.setCellValue(info.getWtDwdz());
        XSSFCell c69_5 = sheet.getRow(69).getCell(5);
        c69_5.setCellValue(info.getCyz());
        XSSFCell c70_5 = sheet.getRow(70).getCell(5);
        c70_5.setCellValue(info.getCyzDz());
        XSSFCell c72_5 = sheet.getRow(72).getCell(5);
        XSSFCell c72_23 = sheet.getRow(72).getCell(23);
        c72_5.setCellValue(info.getSbjbJxmc());
        c72_23.setCellValue(info.getSbjbJxhp());
        XSSFCell c73_5 = sheet.getRow(73).getCell(5);
        XSSFCell c73_23 = sheet.getRow(73).getCell(23);
        c73_5.setCellValue(info.getSbjbJxxh());
        c73_23.setCellValue(info.getSbjbJxbm());
        XSSFCell c74_5 = sheet.getRow(74).getCell(5);
        XSSFCell c74_23 = sheet.getRow(74).getCell(23);
        c74_5.setCellValue(info.getSbjbSccj());
        c74_23.setCellValue(info.getSbjbJxpp());
        XSSFCell c75_5 = sheet.getRow(75).getCell(5);
        XSSFCell c75_23 = sheet.getRow(75).getCell(23);
        c75_5.setCellValue(info.getSbjbSbys());
        c75_23.setCellValue(info.getSbjbCcsj());
        XSSFCell c76_5 = sheet.getRow(76).getCell(5);
        XSSFCell c76_23 = sheet.getRow(76).getCell(23);
        c76_5.setCellValue(info.getSbjbJxlx());
        c76_23.setCellValue(info.getSbjbSblx());
        XSSFCell c77_5 = sheet.getRow(77).getCell(5);
        XSSFCell c77_23 = sheet.getRow(77).getCell(23);
        c77_5.setCellValue(info.getSbjbHclxt());
        c77_23.setCellValue(info.getSbjbGztx());
        XSSFCell c79_5 = sheet.getRow(79).getCell(5);
        XSSFCell c79_23 = sheet.getRow(79).getCell(23);
        c79_5.setCellValue(info.getFdjSccj());
        c79_23.setCellValue(info.getFdjXh());
        XSSFCell c80_5 = sheet.getRow(80).getCell(5);
        XSSFCell c80_23 = sheet.getRow(80).getCell(23);
        c80_5.setCellValue(info.getFdjCcbh());
        c80_23.setCellValue(info.getFdjCcsj());
        XSSFCell c81_5 = sheet.getRow(81).getCell(5);
        XSSFCell c81_23 = sheet.getRow(81).getCell(23);
        c81_5.setCellValue(info.getFdjEdjgl());
        c81_23.setCellValue(info.getFdjEdzs());
        XSSFCell c82_5 = sheet.getRow(82).getCell(5);
        XSSFCell c82_23 = sheet.getRow(82).getCell(23);
        c82_5.setCellValue(info.getFdjRlxh());
        c82_23.setCellValue(info.getFdjPfjd());
        XSSFCell c84_5 = sheet.getRow(84).getCell(5);
        XSSFCell c84_23 = sheet.getRow(84).getCell(23);
        c84_5.setCellValue(info.getJcxxJcyj());
        c84_23.setCellValue(info.getJcxxJcff());
        XSSFCell c85_5 = sheet.getRow(85).getCell(5);
        XSSFCell c85_23 = sheet.getRow(85).getCell(23);
        c85_5.setCellValue(info.getJcxxJclb());
        c85_23.setCellValue(info.getJcxxJccs());
        XSSFCell c86_11 = sheet.getRow(86).getCell(11);
        XSSFCell c86_29 = sheet.getRow(86).getCell(29);
        XSSFCell c86_5 = sheet.getRow(86).getCell(5);
        XSSFCell c86_23 = sheet.getRow(86).getCell(23);
        c86_11.setCellValue(info.getJcxxKssj());
        c86_29.setCellValue(info.getJcxxJssj());
        c86_5.setCellValue(info.getJcrq());
        c86_23.setCellValue(info.getJcrq());
        XSSFCell c87_5 = sheet.getRow(87).getCell(5);
        XSSFCell c87_27 = sheet.getRow(87).getCell(27);
        c87_5.setCellValue(info.getJcxxJcdd());
        c87_27.setCellValue(info.getJcxxSsqy());
        XSSFCell c88_5 = sheet.getRow(88).getCell(5);
        XSSFCell c88_27 = sheet.getRow(88).getCell(13);
        XSSFCell c88_21 = sheet.getRow(88).getCell(21);
        XSSFCell c88_29 = sheet.getRow(88).getCell(29);
        c88_5.setCellValue(info.getJcxxWd());
        c88_27.setCellValue(info.getJcxxXdsd());
        c88_21.setCellValue(info.getJcxxDqyl());
        c88_29.setCellValue(info.getJcxxHbgd());
        XSSFCell c89_5 = sheet.getRow(89).getCell(5);
        c89_5.setCellValue(info.getJcxxJcry());
        XSSFCell c92_14 = sheet.getRow(92).getCell(14);
        XSSFCell c92_24 = sheet.getRow(92).getCell(24);
        XSSFCell c92_27 = sheet.getRow(92).getCell(27);
        c92_14.setCellValue(info.getJcsjPjz());
        c92_24.setCellValue(info.getJcsjJzgpqy());
        c92_27.setCellValue(info.getJcsjJcjg());
        XSSFCell c93_5 = sheet.getRow(93).getCell(5);
        XSSFCell c93_8 = sheet.getRow(93).getCell(8);
        XSSFCell c93_11 = sheet.getRow(93).getCell(11);
        XSSFCell c93_24 = sheet.getRow(93).getCell(24);
        XSSFCell c93_27 = sheet.getRow(93).getCell(27);
        c93_5.setCellValue(info.getJcsjClz1());
        c93_8.setCellValue(info.getJcsjClz2());
        c93_11.setCellValue(info.getJcsjClz3());
        c93_24.setCellValue(info.getJcsjFjzgpq());
        c93_27.setCellValue(info.getJcsjFjcjg());
        XSSFCell c94_5 = sheet.getRow(94).getCell(5);
        XSSFCell c94_24 = sheet.getRow(94).getCell(24);
        c94_5.setCellValue(info.getJcsjZs());
        c94_24.setCellValue(info.getJcsjYqhd());
        SysVorAudit audit = vorAuditMapper.selectByVorId(info.getVorId());
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");
        XSSFCell c97_23 = sheet.getRow(97).getCell(23);
        c97_23.setCellValue(sdf.format(audit.getRatifyTime()));
        SysUser bzr = userService.getSysUserByUId(info.getUserId());
        SysUser shr = userService.getSysUserByUId(audit.getAuditBy());
        SysUser qfr = userService.getSysUserByUId(audit.getRatifyBy());
        try {
            insertPicCost(wb, sheet,FileConstants.QRCODE_IMG_LOCAL_URL,"/"+info.getBgbh()+".jpg",25,1,28,4);
            insertPicCost(wb, sheet,FileConstants.SIGNATURE_IMG_LOCAL_URL,bzr.getSignature(),3,99,11,100);
            insertPicCost(wb, sheet,FileConstants.SIGNATURE_IMG_LOCAL_URL,shr.getSignature(),14,99,22,100);
            insertPicCost(wb, sheet,FileConstants.SIGNATURE_IMG_LOCAL_URL,qfr.getSignature(),25,99,33,100);
        } catch (Exception e) {
            e.printStackTrace();
        }
        XSSFCell c104_5 = sheet.getRow(104).getCell(0);
        XSSFCell c104_6 = sheet.getRow(104).getCell(6);
        XSSFCell c104_11 = sheet.getRow(104).getCell(11);
        XSSFCell c104_15 = sheet.getRow(104).getCell(15);
        XSSFCell c104_23 = sheet.getRow(104).getCell(23);
        XSSFCell c104_29 = sheet.getRow(104).getCell(29);
        try {
            c104_5.setCellValue(info.getZysbSbmc());
            c104_6.setCellValue(info.getZysbSbbh());
            c104_11.setCellValue(info.getZysbXhgg());
            c104_15.setCellValue(info.getZysbSydw());
            c104_23.setCellValue(info.getZysbZsbh());
            c104_29.setCellValue(info.getZysbYxqz());
            insertPicCost(wb, sheet,FileConstants.VOR_IMG_LOCAL_URL,info.getZpJxzt(),0,109,16,110);
            insertPicCost(wb, sheet,FileConstants.VOR_IMG_LOCAL_URL,info.getZpSbjc(),17,109,33,110);
            insertPicCost(wb, sheet,FileConstants.VOR_IMG_LOCAL_URL,info.getZpClmp(),0,111,16,112);
            insertPicCost(wb, sheet,FileConstants.VOR_IMG_LOCAL_URL,info.getZpFdjmp(),17,111,33,112);
            insertPicCost(wb, sheet,FileConstants.VOR_IMG_LOCAL_URL,info.getZpHclzz(),0,113,16,114);
            insertPicCost(wb, sheet,FileConstants.VOR_IMG_LOCAL_URL,info.getZpJcjg(),17,113,33,114);
        } catch (Exception e) {
            e.printStackTrace();
        }
        response.setContentType("application/vnd.ms-excel");
//        response.setHeader("content-size",wb.get);
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(info.getBgbh() + ".xlsm", "utf-8"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        OutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 特殊模板填充下载
     * @param info
     */
    @Override
    public void downVorExcel2(SysVor info, HttpServletResponse response) {
        XSSFWorkbook wb = null;
        try {
            wb = new XSSFWorkbook(new FileInputStream(FileConstants.TEMPLATE_LOCAL_URL+"1598605720995.xlsm"));
        } catch (IOException e) {
            e.printStackTrace();
        }
        XSSFSheet sheet = wb.getSheetAt(0);
        sheet.getRow(2).getCell(23).setCellValue(info.getBgbh());
        sheet.getRow(35).getCell(3).setCellValue(info.getBgbh());
        sheet.getRow(73).getCell(3).setCellValue(info.getBgbh());
        sheet.getRow(103).getCell(3).setCellValue(info.getBgbh());
        sheet.getRow(13).getCell(10).setCellValue(info.getSbjbJxmc());
        sheet.getRow(14).getCell(10).setCellValue(info.getSbjbJxxh());
        sheet.getRow(16).getCell(10).setCellValue(info.getSbjbJxbm());
        sheet.getRow(17).getCell(10).setCellValue(info.getWtDw());
        sheet.getRow(73).getCell(12).setCellValue(info.getJcrq());
        sheet.getRow(75).getCell(6).setCellValue(info.getSbjbJxlx());
        sheet.getRow(75).getCell(19).setCellValue(info.getSbjbJxmc());
        sheet.getRow(76).getCell(6).setCellValue(info.getSbjbJxxh());
        sheet.getRow(76).getCell(19).setCellValue(info.getSbjbJxbm());
        sheet.getRow(77).getCell(6).setCellValue(info.getFdjCcsj());
        sheet.getRow(77).getCell(19).setCellValue(info.getFdjPfjd());
        sheet.getRow(78).getCell(6).setCellValue(info.getFdjXh());
        sheet.getRow(78).getCell(19).setCellValue(info.getFdjEdjgl());
        sheet.getRow(79).getCell(6).setCellValue(info.getSbjbSccj());
        sheet.getRow(79).getCell(24).setCellValue(info.getSbjbJxhp());

        sheet.getRow(80).getCell(6).setCellValue(info.getFdjEdzs());
        sheet.getRow(80).getCell(15).setCellValue(info.getSbjbHclxt());
        sheet.getRow(80).getCell(24).setCellValue(info.getFdjSccj());

        //todo 不确定
        sheet.getRow(82).getCell(6).setCellValue(info.getWtDw());
        sheet.getRow(82).getCell(24).setCellValue(info.getWtLxfs());

        sheet.getRow(83).getCell(6).setCellValue(info.getWtDwdz());

        sheet.getRow(86).getCell(6).setCellValue(info.getJcxxWd());
        sheet.getRow(86).getCell(14).setCellValue(info.getJcxxDqyl());
        sheet.getRow(86).getCell(22).setCellValue(info.getJcxxXdsd());
        sheet.getRow(86).getCell(30).setCellValue(info.getJcxxHbgd());


        //todo 不确定
        sheet.getRow(88).getCell(6).setCellValue(info.getZysbSbmc());
        sheet.getRow(89).getCell(6).setCellValue(info.getZysbSydw());


        sheet.getRow(91).getCell(6).setCellValue(info.getJcxxJcff());
        //todo 不确定
        sheet.getRow(91).getCell(32).setCellValue(info.getFdjPfjd());


        sheet.getRow(95).getCell(6).setCellValue(info.getFdjEdzs());
//        sheet.getRow(95).getCell(10).setCellValue(info.getFdjPfjd());  实际转速
        sheet.getRow(95).getCell(14).setCellValue(info.getJcsjClz1());
        sheet.getRow(95).getCell(17).setCellValue(info.getJcsjClz2());
        sheet.getRow(95).getCell(20).setCellValue(info.getJcsjClz3());
        sheet.getRow(95).getCell(23).setCellValue(info.getJcsjPjz());
//        sheet.getRow(95).getCell(28).setCellValue(info.getXz());   限值  要经过计算
//        sheet.getRow(96).getCell(6).setCellValue(info.getJgpd());  结果判定
        sheet.getRow(96).getCell(25).setCellValue(info.getJcxxJccs());
        SysVorAudit audit = vorAuditMapper.selectByVorId(info.getVorId());
        SysUser bzr = userService.getSysUserByUId(info.getUserId());
        SysUser shr = userService.getSysUserByUId(audit.getAuditBy());
        SysUser qfr = userService.getSysUserByUId(audit.getRatifyBy());
        try {
            insertPicCost(wb, sheet,FileConstants.SIGNATURE_IMG_LOCAL_URL,bzr.getSignature(),6,98,12,99);
            insertPicCost(wb, sheet,FileConstants.SIGNATURE_IMG_LOCAL_URL,shr.getSignature(),17,98,23,99);
            insertPicCost(wb, sheet,FileConstants.SIGNATURE_IMG_LOCAL_URL,qfr.getSignature(),27,98,33,99);
        } catch (Exception e) {
            e.printStackTrace();
        }

        sheet.getRow(99).getCell(6).setCellValue(info.getJcxxJcdd());
        sheet.getRow(99).getCell(25).setCellValue(info.getJcxxKssj());
        sheet.getRow(99).getCell(29).setCellValue(info.getJcxxJssj());
        try {
            insertPicCost(wb, sheet,FileConstants.VOR_IMG_LOCAL_URL,info.getZpJxzt(),0,106,16,107);
            insertPicCost(wb, sheet,FileConstants.VOR_IMG_LOCAL_URL,info.getZpSbjc(),17,106,33,107);
            insertPicCost(wb, sheet,FileConstants.VOR_IMG_LOCAL_URL,info.getZpClmp(),0,108,16,109);
            insertPicCost(wb, sheet,FileConstants.VOR_IMG_LOCAL_URL,info.getZpFdjmp(),17,108,33,109);
            insertPicCost(wb, sheet,FileConstants.VOR_IMG_LOCAL_URL,info.getZpHclzz(),0,110,16,111);
            insertPicCost(wb, sheet,FileConstants.VOR_IMG_LOCAL_URL,info.getZpJcjg(),17,110,33,111);
        } catch (Exception e) {
            e.printStackTrace();
        }
        response.setContentType("application/vnd.ms-excel");
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(info.getBgbh() + ".xlsm", "utf-8"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        OutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 南阳模板3 填充下载
     * @param info
     * @param response
     */
    @Override
    public void downVorExcel3(SysVor info, HttpServletResponse response) {
        XSSFWorkbook wb = null;
        try {
            wb = new XSSFWorkbook(new FileInputStream(FileConstants.TEMPLATE_LOCAL_URL+"1600052885202.xlsm"));
        } catch (IOException e) {
            e.printStackTrace();
        }
        XSSFSheet sheet = wb.getSheetAt(0);
        sheet.getRow(8).getCell(0).setCellValue(info.getBgbh());
        sheet.getRow(13).getCell(9).setCellValue(info.getBgbh());
        sheet.getRow(35).getCell(0).setCellValue(info.getBgbh());

        sheet.getRow(92).getCell(4).setCellValue(info.getBgbh());


        sheet.getRow(14).getCell(9).setCellValue(info.getCyz());   //warn 姓名单位  ---> 持有者姓名
        sheet.getRow(16).getCell(9).setCellValue(info.getSbjbJxlx());
        sheet.getRow(17).getCell(9).setCellValue(info.getSbjbJxxh());
        sheet.getRow(18).getCell(9).setCellValue(info.getSbjbSccj());

        sheet.getRow(61).getCell(4).setCellValue(info.getBgbh());
        sheet.getRow(61).getCell(17).setCellValue(info.getJcrq());
        sheet.getRow(62).getCell(17).setCellValue(info.getJcxxJcff());

        sheet.getRow(65).getCell(4).setCellValue(info.getCyz());
        sheet.getRow(65).getCell(27).setCellValue(info.getCyzLxfs());
        sheet.getRow(66).getCell(4).setCellValue(info.getCyzDz());

        sheet.getRow(68).getCell(4).setCellValue(info.getSbjbJxbm());
        sheet.getRow(68).getCell(14).setCellValue(info.getSbjbJxlx());
//        sheet.getRow(68).getCell(24).setCellValue(info.get());   warn 信息公开号/ 形式核准号

        sheet.getRow(69).getCell(4).setCellValue(info.getSbjbJxxh());
//        sheet.getRow(69).getCell(14).setCellValue(info.get());   warn 机械环保代码 / 产品识别码
        sheet.getRow(69).getCell(24).setCellValue(info.getSbjbSccj());


        sheet.getRow(70).getCell(4).setCellValue(info.getFdjCcsj());
        sheet.getRow(70).getCell(14).setCellValue(info.getFdjPfjd());
        sheet.getRow(70).getCell(24).setCellValue(info.getFdjSccj());

        sheet.getRow(71).getCell(4).setCellValue(info.getFdjXh());
        sheet.getRow(71).getCell(14).setCellValue(info.getFdjEdjgl());
        sheet.getRow(71).getCell(24).setCellValue(info.getFdjEdzs());

//        sheet.getRow(72).getCell(4).setCellValue(info.get);  warn 排气后处理器类型
//        sheet.getRow(72).getCell(14).setCellValue(info.get); warn 后处理型号
//        sheet.getRow(72).getCell(24).setCellValue(info.get);  warn 后处理器生产厂

        sheet.getRow(75).getCell(0).setCellValue(info.getZysbSbbh());
        sheet.getRow(75).getCell(4).setCellValue(info.getZysbSbmc());
        sheet.getRow(75).getCell(11).setCellValue(info.getZysbXhgg());
        sheet.getRow(75).getCell(15).setCellValue(info.getZysbSydw());
        sheet.getRow(75).getCell(23).setCellValue(info.getZysbZsbh());
        sheet.getRow(75).getCell(29).setCellValue(info.getZysbYxqz());

        sheet.getRow(78).getCell(4).setCellValue(info.getJcxxWd());
        sheet.getRow(78).getCell(16).setCellValue(info.getJcxxDqyl());
        sheet.getRow(78).getCell(24).setCellValue(info.getJcxxXdsd());

        sheet.getRow(80).getCell(4).setCellValue(info.getJcxxKssj());
        sheet.getRow(80).getCell(16).setCellValue(info.getJcxxJssj());
        sheet.getRow(80).getCell(24).setCellValue(info.getJcxxJcdd());

//        sheet.getRow(82).getCell(8).setCellValue();   warn 光吸收系数 三次
//        sheet.getRow(82).getCell(12).setCellValue();
//        sheet.getRow(82).getCell(16).setCellValue();
//        sheet.getRow(82).getCell(20).setCellValue();   warn 检验方法
//        sheet.getRow(82).getCell(26).setCellValue();   warn 目视烟羽


//        sheet.getRow(83).getCell(8).setCellValue();   warn 发动机三次转速
//        sheet.getRow(83).getCell(12).setCellValue();
//        sheet.getRow(83).getCell(16).setCellValue();


//        sheet.getRow(84).getCell(8).setCellValue(); warn 计算结果

//        sheet.getRow(85).getCell(8).setCellValue();  warn 排放限值

//        sheet.getRow(86).getCell(8).setCellValue();   warn 结果判定

        try {
            insertPicCost(wb, sheet,FileConstants.VOR_IMG_LOCAL_URL,info.getZpJxzt(),0,89,16,90);
            insertPicCost(wb, sheet,FileConstants.VOR_IMG_LOCAL_URL,info.getZpSbjc(),17,89,33,90);
            insertPicCost(wb, sheet,FileConstants.VOR_IMG_LOCAL_URL,info.getZpClmp(),0,94,16,95);
            insertPicCost(wb, sheet,FileConstants.VOR_IMG_LOCAL_URL,info.getZpFdjmp(),17,94,33,95);
            insertPicCost(wb, sheet,FileConstants.VOR_IMG_LOCAL_URL,info.getZpHclzz(),0,96,16,97);
            insertPicCost(wb, sheet,FileConstants.VOR_IMG_LOCAL_URL,info.getZpJcjg(),17,96,33,97);
        } catch (Exception e) {
            e.printStackTrace();
        }
        response.setContentType("application/vnd.ms-excel");
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(info.getBgbh() + ".xlsm", "utf-8"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        OutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<SysVor> getByStatus(Integer status) {
        return vorMapper.selectByStatus(status);
    }


    @Override
    public String generatorSerialNumber(String ndq,String rq) {
        //状态为已经签发的
        List<SysVor> lists =  getByStatus(SysConstants.REPORT_STATUS4);
        //过滤看是否有多少是包含   当前日期 和 地区首字母
        lists = lists.stream().filter((sysVor) ->
                !StringUtils.isEmpty(sysVor.getBgbh()) && sysVor.getBgbh().contains(ndq) && sysVor.getBgbh().contains(rq)
        ).collect(Collectors.toList());
        int lsh = lists.size() + 1;
        if(lsh < 10){
            return "00"+lsh;
        }else if(lists.size() < 100){
            return "0"+lsh;
        }else{
            return ""+lsh;
        }
    }


    @Override
    public String generatorQRCode(Integer vid) {
        return null;
    }

    /**
     * 生成报告编号
     * 生成规则 :
     * (国测)GC + (2020年取后两位)20 +(地区首拼,XX市)XX的首拼 + (审核员姓的首拼) +  (月,各位数前补0) + (日,各位数前补0)日 + (流水号 , 按照当天地区的报告数开始 从001开始 , 具体看方法执行)
     * @param vor
     * @return
     */
    @Override
    public StringBuffer generatorReportNumber(SysVor vor) {
        SysUser user = (SysUser)SecurityUtils.getSubject().getPrincipal();
        StringBuffer s = new StringBuffer("GC");
        Calendar calendar = Calendar.getInstance();
        int year = calendar.get(Calendar.YEAR);
        int month = (calendar.get(Calendar.MONTH) + 1);
        int day = calendar.get(Calendar.DATE);
        String dq = vor.getJcxxJcdd().substring(vor.getJcxxJcdd().indexOf("省") + 1,vor.getJcxxJcdd().indexOf("市"));
        s.append(year % 100);   //年
        String dqszm = MyUtil.getPingYin(dq); //地区首字母缩写
        String m = month < 10 ? "0" + month : month+"";
        String d = day < 10 ? "0" + day : day+"";
        s.append(dqszm);    //地区首字母
        s.append(MyUtil.getPingYin(user.getHolder().charAt(0) + ""));  //审核员名字第一个字的首字母缩写
        s.append(m);        //月
        s.append(d);        //天
        String sn = generatorSerialNumber((year % 100) + dqszm,m+d);
        s.append(sn);      //流水号
        return s;
    }

    /**
     * 生成委托记录
     *
     * @param vor
     * @return
     */
    @Override
    public SysEntrust generatorEntrustRecord(SysVor vor) {
        //先查询是否有重复的委托单位 , 没有的话就添加 , 有的话就不用添加了 warn 注意添加后返回主键id
        SysClient client;
        SysEntrust entrust = new SysEntrust();
        //对应原始记录id 进行添加
        entrust.setEntrustParty(vor.getWtDw());
        entrust.setCreateTime(new Date());  //warn 委托时间待定 暂时以原始记录创建时间为标准
        entrust.setEntrustDate(vor.getJcrq());
        entrust.setUserId(vor.getUserId());
        entrust.setEntrustAddress(vor.getJcxxJcdd());   //warn 委托地点设置为 检测地点
        entrust.setVorId(vor.getVorId());
        entrust.setInspector(vor.getJcxxJcry());
        return entrust;
    }

    /**
     * 生成设备使用记录
     * @param vor
     * @return
     */
    @Override
    public SysDeviceUse generatorDeviceRecord(SysVor vor){
        SysUserAssociation as = userAssociationService.getByUserId(vor.getUserId());
        SysDevice device = deviceService.getDeviceById(as.getDeviceId());
        SysDeviceUse use = new SysDeviceUse();
        use.setDeviceId(device.getDeviceId());
        use.setCreateTime(new Date());
        use.setUseBy(vor.getUserId());
        use.setUseTime(vor.getJcrq());   //warn 检测日期 作为 设备的使用时间 , 入库时间无法确定
        use.setInspector(vor.getJcxxJcry());
        use.setVorId(vor.getVorId());
        return use;
    }

    private void insertPicCost(XSSFWorkbook workBook, XSSFSheet topPic, String frontUrl,String pic
            , int x1, int x2, int y1, int y2) {
        if (StringUtils.isEmpty(pic)) {
            return;
        } else {
            pic = frontUrl + pic.substring(pic.lastIndexOf("/")+1);
        }
        BufferedImage bufferImg = null;//图片
        try {
            // 先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
            //将图片读到BufferedImage
            bufferImg = ImageIO.read(new File(pic));
            // 将图片写入流中
            ImageIO.write(bufferImg, "png", byteArrayOut);
            // 利用HSSFPatriarch将图片写入EXCEL
            Drawing patriarch = topPic.createDrawingPatriarch();
            //图片一导出到单元格B2中
            XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0,
                    (short) x1, x2, (short) y1, y2);
            // 插入图片
            patriarch.createPicture(anchor, workBook.addPicture(byteArrayOut
                    .toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG));
        } catch (IOException io) {
            log.error("插入失败 : " + pic);
        }
    }
}